﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Data.SqlClient;
using System.Data;
using TSL_Tools.SqlTools;
using System.IO;
using System.Diagnostics;
using System.Globalization;

namespace PerfmonImportExport
{
    /// <summary>
    /// The following code shows how to implement the class and perform an import
    /// "selectedRunId' is a value representing the run id that will receive the new perfmon data
    /// "sqlConn" is the connection string
    /// "fileName" is the fully qualified name of the file containing the perfmon values to import.
    /// "TalkToSql" is a namespace contained in the TSL_Tools dll
    /// 
    ///        string sqlCmd = String.Format("SELECT DISTINCT MachineName FROM LoadTestPerformanceCounterCategory WHERE LoadTestRunId = {0}", selectedRunId);
    ///        TalkToSql tts = new TalkToSql(sqlConn);
    ///        List<string> machines = new List<string>();
    ///        tts.ExecuteReaderStringList(sqlCmd, out machines);
    ///
    ///        PerfmonImportClass pis = new PerfmonImportClass(selectedRunId, sqlConn, 60, 3000, machines);
    ///        string returnValue = pis.PerformTheImport(fileName);
    /// </summary>
    public class PerfmonImportClass
    {
        public static readonly string Sql_GETSTARTINGVALUESFORIMPORTINGPERFMON = @"EXEC TSL_prc_GetStartingValuesForImportingPerfmon @RunId = {0}";
        public static readonly string Sql_UPDATEPERFMONIMPORT = "EXEC TSL_prc_UpdatePerfmonTablesFromImport {0}";

        #region -- Properties -----------------------------------------------------------------------
        public List<PerfmonObjectProperties> perfmonObjects;
        public List<string> machineList;
        public DataTable perfmonSamples { get; set; }
        public bool allowMoreGranularSamples = false;

        private DataTable dataToImport;
        private int startingColumnIndex;
        private int endingColumnIndex;
        private int FIRST_INTERVAL_ID_VALUE = 1;

        // Core info
        private int loadTestRunId;
        private string sqlConn;
        private TalkToSql tts;

        // Starting values needed
        private int startingCounterCategoryId;
        private int startingCounterId;
        private int startingInstanceId;
        private DateTime loadTestStartingDateTime = DateTime.MinValue;
        private DateTime loadTestEndingDateTime = DateTime.MinValue;
        private int sampleIntervalSize;
        private int numberOfSamplesInDB;

        const string noInstance = "systemdiagnosticsperfcounterlibsingleinstance";
        int numberOfCountersInFile = 0; 
        int iTemp = 0;
        #endregion

        #region -- Constructors --------------------------------------------------------------------------
        /// <summary>
        /// Creates a new instance of the class and sets up some initial values and properties
        /// </summary>
        /// <param name="runId">the LoadTestRunId of the run in the LoadTest2010 database that will receive the perfmon counters</param>
        /// <param name="sqlConnection">a valid SQL Connection string for the LoadTest2010 database</param>
        /// <param name="connTimeout">the timeout to use when trying to establish a connection to the database</param>
        /// <param name="cmdTimeout">the timeout to use when waiting for a response from SQL commands that gets executed</param>
        public PerfmonImportClass(int runId, string sqlConnection, int connTimeout, int cmdTimeout)
        {
            perfmonObjects = new List<PerfmonObjectProperties>();
            loadTestRunId = runId;
            machineList = new List<string>();
            tts = new TalkToSql(sqlConnection, connTimeout, cmdTimeout);
            GetStartingIdValues();
            InitializeDataTableForPerfmonSamples();
        }

        /// <summary>
        /// Creates a new instance of the class and sets up some initial values and properties
        /// </summary>
        /// <param name="runId">the LoadTestRunId of the run in the LoadTest2010 database that will receive the perfmon counters</param>
        /// <param name="sqlConnection">a valid SQL Connection string for the LoadTest2010 database</param>
        /// <param name="connTimeout">the timeout to use when trying to establish a connection to the database</param>
        /// <param name="cmdTimeout">the timeout to use when waiting for a response from SQL commands that gets executed</param>
        /// <param name="machines">A list of the machines that already have counters in the database. </param>
        public PerfmonImportClass(int runId, string sqlConnection, int connTimeout, int cmdTimeout, List<string> machines)
        {
            perfmonObjects = new List<PerfmonObjectProperties>();
            loadTestRunId = runId;
            tts = new TalkToSql(sqlConnection, connTimeout, cmdTimeout);
            machineList = machines;
            GetStartingIdValues();
            InitializeDataTableForPerfmonSamples();
        }

        /// <summary>
        /// This builds a DataTable with the same schema as the LoadTestPerformanceCounterSample table in the LoadTest2010 database
        /// It will get populated with the values to add to the DB and then loaded into the DB with SqlBulkCopy
        /// </summary>
        private void InitializeDataTableForPerfmonSamples()
        {
            perfmonSamples = new DataTable("perfmonSamplesTable");
            perfmonSamples.Columns.Add("LoadTestRunId", System.Type.GetType("System.Int32"));
            perfmonSamples.Columns.Add("TestRunIntervalId", System.Type.GetType("System.Int32"));
            perfmonSamples.Columns.Add("InstanceId", System.Type.GetType("System.Int32"));
            perfmonSamples.Columns.Add("ComputedValue", System.Type.GetType("System.Single"));
            perfmonSamples.Columns.Add("RawValue", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("BaseValue", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("CounterFrequency", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("SystemFrequency", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("SampleTimeStamp", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("SampleTimeStamp100nSec", System.Type.GetType("System.Int64"));
            perfmonSamples.Columns.Add("CounterType", System.Type.GetType("System.Int32"));
            perfmonSamples.Columns.Add("ThresholdRuleResult", System.Type.GetType("System.Int16"));
            perfmonSamples.Columns.Add("ThresholdRuleMessageId", System.Type.GetType("System.Int32"));

       }

        /// <summary>
        /// Queries the LoadTest2010 database and gets the highest value for the IDs from each of the counter tables, as
        /// well as the test start time, the number of samples already in the run and the size of the sample interval.
        /// </summary>
        private void GetStartingIdValues()
        {
            string sqlCmd = String.Format(Sql_GETSTARTINGVALUESFORIMPORTINGPERFMON, loadTestRunId);
            DataTable _table = new DataTable();
            tts.ExecuteReaderTable(sqlCmd, out _table);

            loadTestStartingDateTime = (DateTime)_table.Rows[0]["FirstIntervalTime"];
            loadTestEndingDateTime = (DateTime)_table.Rows[0]["LastIntervalTime"];
            startingCounterCategoryId = (int)_table.Rows[0]["CounterCategoryId"];
            startingCounterId = (int)_table.Rows[0]["CounterId"];
            startingInstanceId = (int)_table.Rows[0]["InstanceId"];
            sampleIntervalSize = (int)_table.Rows[0]["IntervalSpan"];
            numberOfSamplesInDB = (int)_table.Rows[0]["IntervalQty"];
        }
        #endregion

        /// <summary>
        /// This is the core method to import the data. It takes a path to a CSV file containing the perfmon counters.
        /// It assumes that a valid test run has already been set during the initialization of the class.
        /// </summary>
        /// <param name="fileName">the path and name of a valid Perfmon CSV file.</param>
        /// <returns></returns>
        public string PerformTheImport(string fileName)
        {
            // ----- Get the data out of the CSV file
            dataToImport = ProcessPerfmonFile(fileName);

            // ----- Check the Interval Size and bail if the intervals do not match
            DateTime dt1 = Convert.ToDateTime(dataToImport.Columns[2].ColumnName);
            DateTime dt2 = Convert.ToDateTime(dataToImport.Columns[3].ColumnName);
            TimeSpan ts = (TimeSpan)(dt2 - dt1);
            int totalSeconds = Convert.ToInt32(ts.TotalSeconds);
            if (totalSeconds != sampleIntervalSize)
            {
                string sTemp = String.Format("Sample Rate of data file was incorrect. Please rebuild CSV file.\r\n" +
                    "TestRunSampleRate = {0}; CSVSampleRate = {1}", sampleIntervalSize, totalSeconds);
                //string sqlCmd2 = String.Format(TSL_ReportingEngine.Sql_INSERT_DBCHANGELOG, loadTestRunId, 4, sTemp);
                //tts.ExecuteNoReturn(sqlCmd2);
                return sTemp;
            }
            // ----- Check the columns to see if there are samples within the starting and ending timeranges.
            startingColumnIndex = GetStartingTimeStampColumnIndex();
            if (startingColumnIndex == 0)
            {
                string sTemp = String.Format("No data added since the sample times for data ({0} to {1}) was outside the range of the test times",
                    dataToImport.Columns[2].ColumnName, dataToImport.Columns[dataToImport.Columns.Count - 1].ColumnName);
                //string sqlCmd2 = String.Format(TSL_ReportingEngine.Sql_INSERT_DBCHANGELOG, loadTestRunId, 4, sTemp);
                //tts.ExecuteNoReturn(sqlCmd2);
                return sTemp;
            }
            endingColumnIndex = GetEndingTimeStampColumnIndex(startingColumnIndex);

            // ----- Now add each set of counter values to the sampleDataTable that will be copied to SQL
            for (int x = 0; x < dataToImport.Rows.Count; x++)
            {
                if(AddInstanceToDataTable(x))
                    numberOfCountersInFile++;
            }

            // ----- Bulk copy the sample data to SQL
            if(numberOfCountersInFile == 0)
            {
                string sTemp = String.Format("No data added since the counters in the CSV file had already been added",
                    dataToImport.Columns[2].ColumnName, dataToImport.Columns[dataToImport.Columns.Count - 1].ColumnName);
                //string sqlCmd2 = String.Format(TSL_ReportingEngine.Sql_INSERT_DBCHANGELOG, loadTestRunId, 4, sTemp);
                //tts.ExecuteNoReturn(sqlCmd2);
                return sTemp;
            }
            string errorMessage = WriteSampleDataToDatabase();
            if (errorMessage != "")
            {
                return errorMessage;
            }

            // ----- Bulk copy the counter properties to the Staging Table in SQL, then execute the sproc to update the 3 primary 
            // performance counter tables in SQL
            WriteCounterInfoDataToDatabase();

            string sReturn = String.Format("Added {0} unique counter instance sets of data: Starting CategoryId={1} Starting CounterId={2} Starting InstanceId={3}"
                , numberOfCountersInFile, startingCounterCategoryId, startingCounterId, startingInstanceId);
            //string sqlCmd3 = String.Format(TSL_ReportingEngine.Sql_INSERT_DBCHANGELOG, loadTestRunId, 4, sReturn);
            //tts.ExecuteNoReturn(sqlCmd3);
            return sReturn;
        }

        #region --Parsing Routines-----------------------------------------------------------------------
        /// <summary>
        /// This method takes a single performance counter instance and adds all of the sample values to
        /// the main samples table locally.
        /// </summary>
        /// <param name="rowId"></param>
        private bool AddInstanceToDataTable(int rowId)
        {
            // First get the counter info. This gets used at the end when the 3 counter properties tables get populated
            PerfmonObjectProperties pop = GetNameFromCsvEntry(dataToImport.Rows[rowId][1].ToString());
            pop.instanceId = (int)dataToImport.Rows[rowId][0];

            // Make sure that the machine for this counter does not already exist in the counterset in SQL
            if(machineList.Count > 0) //if a list of machines was provided, then check this counter. 
            {
                if (machineList.Contains(pop.machineName)) //if it already exists, do not add the counter
                    return false;
            }
            // The machine does not currently exist, so add the counter properties
            perfmonObjects.Add(pop);

            //---------------------------------------------------------------------------------------
            // Now add the rows of sampled data to the samples table.
            int currentIntervalId = GetStartingTestRunIntervalId(startingColumnIndex);
            long floatPrecisionBase = (long)Math.Pow(10, 9);

            for(int x = startingColumnIndex; x <= endingColumnIndex; x++)
            {
                // Need to account for empty values. Not sure when or why they appear, but the item shows up as " " in the CSV file
                double computedValue;
                if(!double.TryParse(dataToImport.Rows[rowId][x].ToString(), out computedValue))
                {
                    computedValue = -1;
                }
                
                long rawValue = (long)(computedValue * (long)Math.Pow(10, 7));

                perfmonSamples.Rows.Add(
                    loadTestRunId
                    , currentIntervalId++
                    , dataToImport.Rows[rowId][0]
                    , computedValue
                    , rawValue
                    , floatPrecisionBase     //BaseValue
                    , 0     //CounterFrequency
                    , 0     //SystemFrequency
                    , GetSystemTimeStampForValues(dataToImport.Columns[x].ColumnName, false)
                    , GetSystemTimeStampForValues(dataToImport.Columns[x].ColumnName, true)
                    , PerformanceCounterType.RawFraction
                    , 0     //ThresholdRuleResult
                    , null  //ThresholdRuleMessageId
                    );
            }
            return true;
        }

        /// <summary>
        /// Add the final samples DataTable to the LoadTest2010 database
        /// </summary>
        /// <returns></returns>
        private string WriteSampleDataToDatabase()
        {
            string errorMessage = "";
            tts.ExecuteSqlBulkCopy("LoadTestPerformanceCounterSample", perfmonSamples, out errorMessage);
            return errorMessage;
        }

        /// <summary>
        /// converts the perfmonObjects collection to a DataTable, copies the table to a staging table in the
        /// LoadTest2010 database, and finally executes a stored procedure that moves the data from the staging
        /// table to the three Performance info tables
        /// </summary>
        /// <returns></returns>
        private string WriteCounterInfoDataToDatabase()
        {
            string errorMessage = "";
            DataTable _table = new DataTable();

            _table.Columns.Add("instanceId", System.Type.GetType("System.Int32"));
            _table.Columns.Add("machineName", System.Type.GetType("System.String"));
            _table.Columns.Add("categoryName", System.Type.GetType("System.String"));
            _table.Columns.Add("counterName", System.Type.GetType("System.String"));
            _table.Columns.Add("instanceName", System.Type.GetType("System.String"));
            _table.Columns.Add("cumulativeValue", System.Type.GetType("System.Single"));

            for (int x = 0; x < perfmonObjects.Count; x++ )
            {
                _table.Rows.Add(perfmonObjects[x].ReturnItemsAsArray());
            }
            tts.ExecuteSqlBulkCopy("TSL_StagingTableForPerfmonImports", _table, out errorMessage);

            int rowCount;
            string sqlCmd = String.Format(Sql_UPDATEPERFMONIMPORT, loadTestRunId);
            tts.ExecuteAndReturnRowCount(sqlCmd, out rowCount);  // currently ignoring row count.
            return errorMessage;

        }
        #endregion

        #region -- Parse CSV File into Data Table ------------------------------------------------
        /// <summary>
        /// This routine opens the CSV file, does some initial checks on the format, and then parses the data into
        /// a temporary DataTable, as follows:
        ///     - The first line in the CSV file is processed separately from the rest 
        ///     - The rest of the lines are added, one at a time
        /// . Once it is all done, the dataTable is pivoted and the results of the pivot are
        /// returned.
        /// 
        /// This creates a DataTable with the following schema:
        /// Column 0 = The instanceId of the counter instance to use
        /// Column 1 = The name of the counter instance (still in the CSV format)
        /// Columns 2 - XX = the sample values for ther instance
        /// </summary>
        /// <param name="fileName">the file path and name of the CSV file that contains the data to import</param>
        /// <returns></returns>
        public DataTable ProcessPerfmonFile(string fileName)
        {
           DataTable CsvDataTable = new DataTable();
           using (StreamReader sr = new StreamReader(fileName))
            {
                string firstLine = sr.ReadLine().Replace("\"", "");   //"(PDH-CSV 4.0) (Central Standard Time)(360)","\\GEOFFGR-SLATE\IPv6\Datagrams Received Delivered/sec","\\GEOFFGR-SLATE\IPv6\Datagrams Received Unknown Protocol",
                if (!firstLine.StartsWith("(PDH"))
                {
                    throw new InvalidDataException(String.Format("CSV Format does not appear to be valid. Starting line is:\r\n\r\n{0}", firstLine.Substring(0, 128)));
                }

               // Build the data row that has all of the counter instances in it
                AddFirstDataRowFromCsvLine(ref CsvDataTable, firstLine, startingInstanceId);
                
               // Loop through all of the rest of the rows, adding the timestamp and all of the sample values for that time
               while (sr.Peek() != -1)
                {
                    string valuesLine = sr.ReadLine();
                    CsvDataTable.Rows.Add(valuesLine.Replace("\"", "").Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries));
                }
            }

            // Now that the data is complete, pivot the table so that the columns contain the timestamps and each row
            // maps to a single counter instance and all of its samples.
           return PivotTable(CsvDataTable, "InstanceId");
        }

        /// <summary>
        /// This routine builds a string array from the string passed in, then adds a column to the passed in DataTable for
        /// each item in the array. It names the columns using incremented values of the instanceId.
        /// Then it adds a new row and sets the values to the raw counter strings from the file.
        /// </summary>
        /// <param name="_table">a reference to the DataTable that will get populated.</param>
        /// <param name="CsvFileLineString">the first line from the CSV file</param>
        /// <param name="startingIdForColumn">an integer that represents the first value to use for the instanceIds</param>
        private void AddFirstDataRowFromCsvLine(ref DataTable _table, string CsvFileLineString, int startingIdForColumn)
        {
            int x = startingIdForColumn;                
            foreach (string str in CsvFileLineString.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
            {
                _table.Columns.Add(x.ToString());
                x++;
            }
            _table.Rows.Add(CsvFileLineString.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries));

        }

        /// <summary>
        /// A generic routine that creates a DataTable that is a pivoted version of the DataTable passed in.
        /// </summary>
        /// <param name="source">The DataTable to pivot</param>
        /// <param name="colZeroName">the name to apply to the first column from the pivoted table.</param>
        /// <returns></returns>
        private DataTable PivotTable(DataTable source, string colZeroName)
        {
            DataTable dest = new DataTable();
            dest.Columns.Add(colZeroName, System.Type.GetType("System.Int32"));

            foreach (DataRow r in source.Rows)
            {
                dest.Columns.Add(r[0].ToString());
            }

            for (int i = 1; i < source.Columns.Count; i++)
            {
                DataRow row = dest.NewRow();
                row[0] = source.Columns[i].ColumnName;
                dest.Rows.Add(row);
            }

            for (int row = 0; row < source.Rows.Count; row++)
            {
                for (int col = 1; col < source.Columns.Count; col++)
                {
                    dest.Rows[col - 1][row + 1] = source.Rows[row][col];
                }
            }

            dest.AcceptChanges();
            return dest;
        }

        /// <summary>
        /// This routine parses a raw CSV counter name and generates a <see cref="PerfmonObjectProperties"/> instance with the 
        /// parsed information. It populates the MachineName, CategoryName, CounterName and InstanceName. The other
        /// properties of the object will get populated later
        /// </summary>
        /// <param name="csvEntry">the string containing a raw counterInstance name from the CSV File</param>
        /// <returns>a <see cref="PerfmonObjectProperties"/> object</returns>
        private PerfmonObjectProperties GetNameFromCsvEntry(string csvEntry)
        {
            int categoryStart;
            int counterStart;
            int instanceStart;
            int instanceLength;

            //Name Format:	 
            //  \\<Machine>\<CategoryName>[(<InstanceName>)]\<CounterName>
            // If (<InstanceName>) is not present, then instanceName in DB becomes systemdiagnosticsperfcounterlibsingleinstance

            PerfmonObjectProperties props = new PerfmonObjectProperties();
            if (!csvEntry.StartsWith("\\\\"))
                throw new Exception(String.Format("Invalid csv file entry for Perfmon Import used. {0}", csvEntry));

            // ----- Machine Name -----
            iTemp = csvEntry.IndexOf("\\", 2);
            if (iTemp == -1)
                throw new Exception(String.Format("Invalid csv file entry for Perfmon Import used. {0}", csvEntry));
            props.machineName = csvEntry.Substring(2, iTemp - 2);

            // ----- Category Name -----
            categoryStart = iTemp + 1;
            iTemp = csvEntry.IndexOf("\\", categoryStart);
            if (iTemp == -1)
                throw new Exception(String.Format("Invalid csv file entry for Perfmon Import used. {0}", csvEntry));
            props.categoryName = csvEntry.Substring(categoryStart, iTemp - categoryStart);

            // ----- Counter Name -----
            counterStart = iTemp + 1;
            iTemp = csvEntry.IndexOf("\\", counterStart);
            if (iTemp != -1) // NOTE: We should NOT find another delimiter so this should always = -1
                throw new Exception(String.Format("Invalid csv file entry for Perfmon Import used. {0}", csvEntry));
            props.counterName = csvEntry.Substring(counterStart);

            // ----- clean up category ------
            instanceStart = props.categoryName.IndexOf("(");
            if (instanceStart == -1) // no instance
            {
                props.instanceName = noInstance;
            }
            else
            {
                instanceLength = props.categoryName.IndexOf(")") - (instanceStart + 1);
                props.instanceName = props.categoryName.Substring(instanceStart + 1, instanceLength);
                props.categoryName = props.categoryName.Substring(0, instanceStart);
            }

            return props;
        }
        #endregion

        #region -- Utility methods ---------------------------------------------------------------------
        /// <summary>
        /// converts a string representing a date and time to a <see cref="DateTime"/> object and compares
        /// that to the starting and ending times of the test run. If the starting and ending times were never
        /// set, then we bail completely (return -1). If the times were set, but the passed in time is not 
        /// within that range, then return 0.
        /// 
        /// If the time qualifies as valid, use the <see cref="DateTime.ToFileTime()"/> method to convert the
        /// value to a <see cref="System.Int64"/> type. then determine the resolution and return the value
        /// </summary>
        /// <param name="dateTimeStamp">the string with the DateTime value</param>
        /// <param name="use100nsec">a flag indicating whether the resolution is in msec or nsec</param>
        /// <returns><see cref="System.Int64"/></returns>
        private Int64 GetSystemTimeStampForValues(string dateTimeStamp, bool use100nsec)
        {
            DateTime dt = Convert.ToDateTime(dateTimeStamp);
                        
            if(loadTestStartingDateTime == DateTime.MinValue || loadTestEndingDateTime == DateTime.MinValue)
            {
                return -1; // Initial values were not set. Bail out completely
            }
            if(dt <= loadTestStartingDateTime || loadTestEndingDateTime <= dt)
            {
                return 0;  // Value is outside of the loadtest timeframe. Don't use this line of data.
            }

            Int64 dateTime = dt.ToFileTime();
            if (use100nsec)
                return dateTime;
            else
                return dateTime / 1000;
        }

        /// <summary>
        /// This routine quickly walks all of the times in the <see cref="dataToImport"/> DataTable
        /// and returns the column number of the first column to contain a valid TimeStamp. This allows
        /// the class to narrow the scope of work needed to process the file by ignoring columns with
        /// data that is not in the time range of the test.
        /// </summary>
        /// <returns>the column index of the first column with a valid time</returns>
        private int GetStartingTimeStampColumnIndex()
        {
            Int64 SampleTimeStamp = 0;
            for (int a = 2; a < dataToImport.Columns.Count; a++)
            {
                SampleTimeStamp = GetSystemTimeStampForValues(dataToImport.Columns[a].ColumnName, false);
                if (SampleTimeStamp == -1)
                    throw new ArgumentNullException("Invalid Time stamps from the LoadTestRun results");
                if (SampleTimeStamp != 0)
                {
                    // Found a valid time stamp
                    return a;
                }
            }
            return 0;
        }

        /// <summary>
        /// This routine quickly walks all of the times in the <see cref="dataToImport"/> DataTable
        /// and returns the column number of the first column to contain a valid TimeStamp. This allows
        /// the class to narrow the scope of work needed to process the file by ignoring columns with
        /// data that is not in the time range of the test.
        /// </summary>
        /// <param name="startingColumnIndex">the column index from the GetStartingTimeStampColumnIndex method</param>
        /// <returns>the column index of the last column with a valid time</returns>
        private int GetEndingTimeStampColumnIndex(int startingColumnIndex)
        {
            Int64 SampleTimeStamp = 0;
            for (int a = startingColumnIndex + 1; a < dataToImport.Columns.Count; a++)
            {
                SampleTimeStamp = GetSystemTimeStampForValues(dataToImport.Columns[a].ColumnName, false);
                if (SampleTimeStamp == -1)
                    throw new ArgumentNullException("Invalid Time stamps from the LoadTestRun results");
                if (SampleTimeStamp == 0)
                {
                    // Found a valid time stamp
                    return a - 1;
                }
            }
            return dataToImport.Columns.Count;
        }

        private int GetStartingTestRunIntervalId(int startingColumnIndex)
        {
            if(startingColumnIndex > 2)
            {
                // the CSV file had columns of data PRIOR to the start of the test, so we know
                // we can start adding the data at the very first location in the test
                return FIRST_INTERVAL_ID_VALUE;
            }
            else
            {
                string str = @"
SELECT TOP 1 TestRunIntervalId FROM LoadTestRunInterval
WHERE LoadTestRunId = {0} AND IntervalEndTime > '{1}'
ORDER BY IntervalEndTime ASC
";
                string sqlCmd = String.Format(str, loadTestRunId, dataToImport.Columns[startingColumnIndex].ColumnName);
                int returnValue;
                tts.ExecuteScalarInt32(sqlCmd, out returnValue);
                return returnValue;
            }
        }
        #endregion
 
    }

    /// <summary>
    /// The following code shows how to implement the class and perform an import
    /// "selectedRunId' is a value representing the run id that will receive the new perfmon data
    /// "machineName" is the name of the machine in the results collection that you want to export the counters for
    /// "sqlConn" is the connection string
    /// "fileFolderLocation" is the fully qualified path of the folder to create the export file (the filename is auto-generated).
    /// "TalkToSql" is a namespace contained in the TSL_Tools dll. It is needed for the class to work.
    ///         PerfmonExportClass pes = new PerfmonExportClass(sqlConn, 60, 3000);
    ///         sFileName = pes.ExportPerfmonCounters(machineName, selectedRunId, fileFolderLocation, sqlConn);
    /// </summary>
    public class PerfmonExportClass
    {
        private TalkToSql tts;
        private DataTable _exportTable;
        public static readonly string Sql_PERFCOUNTEREXPORTTOCSV = @"EXEC TSL_prc_PerfCounterExportToCSV @runId={0}, @InstanceName={1}";

        public PerfmonExportClass(string sqlConnection, int connTimeout, int cmdTimeout)
        {
            tts = new TalkToSql(sqlConnection, connTimeout, cmdTimeout);
        }

        public string ExportPerfmonCounters(string machine, int LoadTestRunId, string FolderToStoreResults, string sqlConnection)
        {

            string machineName = String.Format("'\\\\{0}\\%'", machine);

            string sqlCmd = String.Format(Sql_PERFCOUNTEREXPORTTOCSV, LoadTestRunId, machineName);
            string sqlConn = sqlConnection;
            DataTable _table;
            TimeSpan ts = tts.ExecuteReaderTable(sqlCmd, out _table);


            string sFileName = String.Format("{0}Run{1}-{2}-Perfmon.csv",
                FolderToStoreResults,
                LoadTestRunId, machineName.Replace('\\', ' '));
            sFileName = sFileName.Replace('%', ' ');
            sFileName = sFileName.Replace('\'', ' ');
            sFileName = sFileName.Replace(" ", "");

            StringBuilder sb = new StringBuilder();
            IEnumerable<string> columnNames = _table.Columns.Cast<DataColumn>().
                                                Select(column => column.ColumnName);
            sb.AppendLine("\"" + string.Join("\",\"", columnNames) + "\"");
            List<string> fields = new List<string>();
            foreach (DataRow row in _table.Rows)
            {
                fields.Clear();
                for (int x = 0; x < row.ItemArray.Length; x++)
                {
                    if (x == 0)
                        fields.Add(((DateTime)row.ItemArray[0]).ToString("G", DateTimeFormatInfo.InvariantInfo));

                    else
                        fields.Add(row.ItemArray[x].ToString());
                }
                sb.AppendLine("\"" + string.Join("\",\"", fields) + "\"");
            }
            File.WriteAllText(sFileName, sb.ToString());
            return sFileName;
        }


        public TimeSpan ExportPerfmonCountersPart1(string machine, int LoadTestRunId, string FolderToStoreResults, string sqlConnection)
        {

            string machineName = String.Format("'\\\\{0}\\%'", machine);

            string sqlCmd = String.Format(Sql_PERFCOUNTEREXPORTTOCSV, LoadTestRunId, machineName);
            string sqlConn = sqlConnection;
            TimeSpan ts = tts.ExecuteReaderTable(sqlCmd, out _exportTable);
            return ts;

        }

        public string ExportPerfmonCountersPart2(string machine, int LoadTestRunId, string FolderToStoreResults, string sqlConnection)
        {
            if(!_exportTable.IsInitialized)
            {
                return "Failed to pull the data from SQL";
            }

            string machineName = String.Format("'\\\\{0}\\%'", machine);

            string sFileName = String.Format("{0}Run{1}-{2}-Perfmon.csv",
                FolderToStoreResults,
                LoadTestRunId, machineName.Replace('\\', ' '));
            sFileName = sFileName.Replace('%', ' ');
            sFileName = sFileName.Replace('\'', ' ');
            sFileName = sFileName.Replace(" ", "");

            StringBuilder sb = new StringBuilder();
            IEnumerable<string> columnNames = _exportTable.Columns.Cast<DataColumn>().
                                                Select(column => column.ColumnName);
            sb.AppendLine("\"" + string.Join("\",\"", columnNames) + "\"");
            List<string> fields = new List<string>();
            foreach (DataRow row in _exportTable.Rows)
            {
                fields.Clear();
                for (int x = 0; x < row.ItemArray.Length; x++)
                {
                    if (x == 0)
                        fields.Add(((DateTime)row.ItemArray[0]).ToString("G", DateTimeFormatInfo.InvariantInfo));

                    else
                        fields.Add(row.ItemArray[x].ToString());
                }
                sb.AppendLine("\"" + string.Join("\",\"", fields) + "\"");
            }
            File.WriteAllText(sFileName, sb.ToString());
            return sFileName;
        }

    }
    
    /// <summary>
    /// 
    /// </summary>
    public class PerfmonObjectProperties
    {
        public int instanceId;

        public string machineName;

        public string categoryName;

        public string counterName;

        public string instanceName;

        public Single cumulativeValue;

        public object[] ReturnItemsAsArray()
        {
            object[] items = new object[6];
            items[0] = instanceId;
            items[1] = machineName;
            items[2] = categoryName;
            items[3] = counterName;
            items[4] = instanceName;
            items[5] = cumulativeValue;
            return items;
        }
    }

}



